package com.tim.dao.Impl;

import com.tim.dao.PlotDao;
import com.tim.pojo.Plot;
import com.tim.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 作者：Tim
 * 时间：2021/4/25 16:37
 */
public class PlotDaoImpl implements PlotDao {

    @Override
    public List<Plot> selectAll() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from plot";
        return qr.query(sql,new BeanListHandler<Plot>(Plot.class));
    }

    @Override
    public int deletePlot(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "delete from plot where id = ?";
        Object[] args = {id};
        return qr.update(sql,args);
    }

    @Override
    public int addPlot(Plot plot) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "insert into plot (id,plotNum,plotName,plotLocation,plotArea,buildNum,familyNum,greenRate,image,developer,tenementName,datecreated,status)values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] args = {plot.getId(),plot.getPlotNum(),plot.getPlotName(),plot.getPlotLocation(),plot.getPlotArea(),plot.getBuildNum(),plot.getFamilyNum(),plot.getGreenRate(),plot.getImage(),plot.getDeveloper(),plot.getTenementName(),plot.getDatecreated(),plot.getStatus()};
        return qr.update(sql,args);
    }

    @Override
    public Plot selectOne(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from plot where id =?";
        Object[] args = {id};
        return qr.query(sql,new BeanHandler<Plot>(Plot.class),args);
    }

    @Override
    public int updatePlot(Plot plot) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "update plot set plotNum=?,plotName=?,plotLocation=?,plotArea=?,buildNum=?,familyNum=?,greenRate=?,image=?,developer=?,tenementName=?,datecreated=?,status=? where id = ?";
        Object[] args={plot.getPlotNum(),plot.getPlotName(),plot.getPlotLocation(),plot.getPlotArea(),plot.getBuildNum(),plot.getFamilyNum(),plot.getGreenRate(),plot.getImage(),plot.getDeveloper(),plot.getTenementName(),plot.getDatecreated(),plot.getStatus(),plot.getId()};
        return qr.update(sql,args);
    }

    @Override
    public List<Plot> searchPlot(String plotName) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from plot where plotName like ?";
        Object[] args = {"%"+plotName+"%"};
        return qr.query(sql,new BeanListHandler<Plot>(Plot.class),args);
    }

    @Override
    public List<Plot> limit(Integer currentPage, Integer pageSize,String plotName) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(plotName == null){
            String sql = "select * from plot limit ?,?";
            Object[] args={(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<Plot>(Plot.class),args);
        }else{
            String sql = "select * from plot where plotName like ? limit ?,?";
            Object[] args={"%"+plotName+"%",(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<Plot>(Plot.class),args);
        }
    }

    @Override
    public int count(String plotName) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(plotName == null){
            String sql = "select count(*) from plot ";
            //new ScalarHandler<泛型类型>()   单行单列用这个  .intValue()转换为int类型
            return qr.query(sql,new ScalarHandler<Number>()).intValue();
        }else{
            String sql = "select count(*) from plot where plotName like ?";
            //new ScalarHandler<泛型类型>()   单行单列用这个  .intValue()转换为int类型
            return qr.query(sql,new ScalarHandler<Number>(),"%"+plotName+"%").intValue();
        }
    }
}
